package com.sailfish.springbootdemo.dao.db4;

import com.sailfish.springbootdemo.common.DB4TableName;
import com.sailfish.springbootdemo.pojo.db4.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;
import java.util.Map;

public interface UserDao {

    /**
     * 登录
     *
     * @param userName          账号
     * @param encryptedPassword 加密后的密码
     * @return
     */
    @Select("select * from " + DB4TableName.USER + " where user_name = #{userName} and password = #{encryptedPassword}")
    @Results(id = "userResultMap", value = {
            @Result(column = "user_id", property = "userId", jdbcType = JdbcType.INTEGER),
            @Result(column = "user_name", property = "userName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "nick_name_cn", property = "nickNameCn", jdbcType = JdbcType.INTEGER),
            @Result(column = "nick_name_en", property = "nickNameEn", jdbcType = JdbcType.INTEGER),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "dept_id", property = "deptId", jdbcType = JdbcType.INTEGER),
            @Result(column = "domain_account", property = "domainAccount", jdbcType = JdbcType.VARCHAR),
            @Result(column = "emp_code", property = "empCode", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept1_name", property = "dept1Name", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept1_code", property = "dept1Code", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept2_name", property = "dept2Name", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept2_code", property = "dept2Code", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept3_name", property = "dept3Name", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept3_code", property = "dept3Code", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept4_name", property = "dept4Name", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept4_code", property = "dept4Code", jdbcType = JdbcType.VARCHAR),
            @Result(column = "role_base", property = "roleBase", jdbcType = JdbcType.VARCHAR),
            @Result(column = "role_tech", property = "roleTech", jdbcType = JdbcType.VARCHAR),
            @Result(column = "role_sys", property = "roleSys", jdbcType = JdbcType.VARCHAR),

            @Result(column = "emp_name", property = "empName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "english_name", property = "englishName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "display_name", property = "displayName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "preferred_name", property = "preferredName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "sex", property = "sex", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept_code", property = "deptCode", jdbcType = JdbcType.VARCHAR),
            @Result(column = "coa", property = "coa", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dept_name", property = "deptName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "notes_id", property = "notesId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "tel_long_num", property = "telLongNum", jdbcType = JdbcType.VARCHAR),
            @Result(column = "tel_short_num", property = "telShortNum", jdbcType = JdbcType.VARCHAR),
            @Result(column = "placeof_perf", property = "placeofPerf", jdbcType = JdbcType.VARCHAR),
            @Result(column = "site", property = "site", jdbcType = JdbcType.VARCHAR),
            @Result(column = "mobile", property = "mobile", jdbcType = JdbcType.VARCHAR),
            @Result(column = "position_name", property = "positionName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "cn_display_name", property = "cnDisplayName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "placeof_performance_ctid", property = "placeofPerformanceCtid", jdbcType = JdbcType.VARCHAR),
            @Result(column = "placeof_performance_ctname", property = "placeofPerformanceCtname", jdbcType = JdbcType.VARCHAR),
            @Result(column = "placeof_assignment_ctid", property = "placeofAssignmentCtid", jdbcType = JdbcType.VARCHAR),
            @Result(column = "placeof_assignment_ctname", property = "placeofAssignmentCtname", jdbcType = JdbcType.VARCHAR),
            @Result(column = "director", property = "director", jdbcType = JdbcType.VARCHAR),
            @Result(column = "director_code", property = "directorCode", jdbcType = JdbcType.VARCHAR),
            @Result(column = "emp_type", property = "empType", jdbcType = JdbcType.VARCHAR),
            @Result(column = "is_inner", property = "isInner", jdbcType = JdbcType.INTEGER),
            @Result(column = "belong_pcb", property = "belongPcb", jdbcType = JdbcType.INTEGER),
            @Result(column = "belong_pcba", property = "belongPcba", jdbcType = JdbcType.INTEGER),
            @Result(column = "belong_pcb_name", property = "belongPcbName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pcbas", property = "pcbas", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pcbs", property = "pcbs", jdbcType = JdbcType.VARCHAR),
    })
    User login(@Param("userName") String userName, @Param("encryptedPassword") String encryptedPassword);

    /**
     * 插入
     *
     * @param user
     * @return
     */
    @Insert("insert into " + DB4TableName.USER + "(user_name,role,dashboard,status,nick_name_cn,password,nick_name_en,email,create_time,dept_id,domain_account,emp_code,dept1_name,dept1_code,dept2_name,dept2_code,dept3_name,dept3_code,dept4_name,dept4_code," +
            "emp_name,english_name,display_name,preferred_name,sex,dept_code,coa,dept_name,notes_id,tel_long_num,tel_short_num,placeof_perf," +
            "site,mobile,position_name,cn_display_name,placeof_performance_ctid,placeof_performance_ctname,placeof_assignment_ctid,placeof_assignment_ctname," +
            "director,director_code,emp_type,is_inner,belong_pcb,belong_pcba,belong_pcb_name) values " +
            "(#{user.userName},#{user.role},#{user.dashboard},#{user.status},#{user.nickNameCn},#{user.password},#{user.nickNameEn},#{user.email},#{user.createTime},#{user.deptId},#{user.domainAccount},#{user.empCode},#{user.dept1Name},#{user.dept1Code},#{user.dept2Name},#{user.dept2Code},#{user.dept3Name},#{user.dept3Code},#{user.dept4Name},#{user.dept4Code}," +
            "#{user.empName},#{user.englishName},#{user.displayName},#{user.preferredName},#{user.sex},#{user.deptCode},#{user.coa},#{user.deptName},#{user.notesId},#{user.telLongNum},#{user.telShortNum},#{user.placeofPerf}," +
            "#{user.site},#{user.mobile},#{user.positionName},#{user.cnDisplayName},#{user.placeofPerformanceCtid},#{user.placeofPerformanceCtname},#{user.placeofAssignmentCtid},#{user.placeofAssignmentCtname}," +
            "#{user.director},#{user.directorCode},#{user.empType},#{user.isInner},#{user.belongPcb},#{user.belongPcba},#{user.belongPcbName})")
    Integer addUser(@Param("user") User user);

    /**
     * 根据userId查询 用户信息
     *
     * @param userId
     * @return
     */
    @Select("select * from " + DB4TableName.USER + " where user_id = #{userId}")
    @ResultMap("userResultMap")
    User getUserInfoById(@Param("userId") Integer userId);

    /**
     * 根据domainAccount查询 用户信息
     *
     * @param domainAccount
     * @return
     */
    @Select("select * from " + DB4TableName.USER + " where domain_account = #{domainAccount} limit 1")
    @ResultMap("userResultMap")
    User getUserInfoByDomainAccount(@Param("domainAccount") String domainAccount);

    /**
     * 根据部门查询所有的员工
     *
     * @param deptId 部门id
     * @return
     */
    @ResultMap("userResultMap")
    @Select("<script>" +
            "select * from " + DB4TableName.USER + " where 1 = 1 " +
            "<if test='deptId != null and deptId != 0 '>" +
            " and dept_id = #{deptId}" +
            "</if>" +
            "<if test='searchName != null and searchName != \"\" '>" +
            " and nick_name_cn like CONCAT('%',#{searchName},'%') or nick_name_en like CONCAT('%',#{searchName},'%') or user_name like CONCAT('%',#{searchName},'%')" +
            "</if>" +
            "<if test='begin != null and pageSize != null and pageSize != \"\" '>" +
            " limit #{begin},#{pageSize}" +
            "</if>" +
            "</script>")
    List<User> getUserListByDept(@Param("deptId") Integer deptId, @Param("searchName") String searchName, @Param("begin") Integer begin, @Param("pageSize") Integer pageSize);

    /**
     * 根据 id列表 查询所有员工
     *
     * @param deptIds 部门id列表
     * @return
     */
    @ResultMap("userResultMap")
    @Select("<script>" +
            "select * from " + DB4TableName.USER + " where 1 = 1 " +
            "<if test='deptIds != null'>" +
            " and dept_id in " +
            "<foreach item='item' index='index' collection='deptIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</if>" +
            "<if test='searchName != null and searchName != \"\" '>" +
            " and nick_name_cn like CONCAT('%',#{searchName},'%') or nick_name_en like CONCAT('%',#{searchName},'%') or user_name like CONCAT('%',#{searchName},'%')" +
            "</if>" +
            "<if test='begin != null and pageSize != null and pageSize != \"\" '>" +
            " limit #{begin},#{pageSize}" +
            "</if>" +
            "</script>")
    List<User> getUserListByDepts(@Param("deptIds") List<Integer> deptIds, @Param("searchName") String searchName, @Param("begin") Integer begin, @Param("pageSize") Integer pageSize);

    /**
     * 查询所有员工
     */
    @Select("<script>" +
            "select * from " + DB4TableName.USER + " where 1 = 1 " +
            "<if test='searchName != null and searchName != \"\" '>" +
            " and (nick_name_cn like CONCAT('%',#{searchName},'%') or display_name like CONCAT('%',#{searchName},'%') or dept1_name like CONCAT('%',#{searchName},'%') or dept2_name like CONCAT('%',#{searchName},'%') or dept3_name like CONCAT('%',#{searchName},'%') or dept4_name like CONCAT('%',#{searchName},'%'))" +
            "</if>" +
            "<if test='begin != null and pageSize != null and pageSize != \"\" '>" +
            " limit #{begin},#{pageSize}" +
            "</if>" +
            "</script>")
    @ResultMap(value = "userResultMap")
    List<User> getAllUser(@Param("searchName") String searchName, @Param("begin") Integer begin, @Param("pageSize") Integer pageSize);

    /**
     * 删除
     *
     * @param userId
     * @return
     */
    @Delete("delete from " + DB4TableName.USER + " where user_id = #{userId}")
    Integer deleteUser(@Param("userId") Integer userId);

    /**
     * 更新
     *
     * @param user
     * @return
     */
    @Update("<script>" +
            "update " + DB4TableName.USER +
            "<set>" +
            "<if test='user.userName != null and user.userName != \"\" '>" +
            " user_name = #{user.userName}," +
            "</if>" +
            "<if test='user.role != null and user.role != \"\" '>" +
            " role = #{user.role}," +
            "</if>" +
            "<if test='user.dashboard != null and user.dashboard != \"\" '>" +
            " dashboard = #{user.dashboard}," +
            "</if>" +
            "<if test='user.status != null and user.status != \"\" '>" +
            " status = #{user.status}," +
            "</if>" +
            "<if test='user.updateTime != null '>" +
            " update_time = #{user.updateTime}," +
            "</if>" +
            "<if test='user.nickNameCn != null and user.nickNameCn != \"\" '>" +
            " nick_name_cn = #{user.nickNameCn}," +
            "</if>" +
            "<if test='user.password != null and user.password != \"\" '>" +
            " password = #{user.password}," +
            "</if>" +
            "<if test='user.nickNameEn != null and user.nickNameEn != \"\" '>" +
            " nick_name_en = #{user.nickNameEn}," +
            "</if>" +
            "<if test='user.email != null and user.email != \"\" '>" +
            " email = #{user.email}," +
            "</if>" +
            "<if test='user.deptId != null and user.deptId != \"\" '>" +
            " dept_id = #{user.deptId}," +
            "</if>" +
            "<if test='user.domainAccount != null and user.domainAccount != \"\" '>" +
            " domain_account = #{user.domainAccount}," +
            "</if>" +
            "<if test='user.empCode != null and user.empCode != \"\" '>" +
            " emp_code = #{user.empCode}," +
            "</if>" +
            "<if test='user.dept1Name != null and user.dept1Name != \"\" '>" +
            " dept1_name = #{user.dept1Name}," +
            "</if>" +
            "<if test='user.empCode != null and user.empCode != \"\" '>" +
            " dept1_code = #{user.dept1Code}," +
            "</if>" +
            "<if test='user.dept2Name != null and user.dept2Name != \"\" '>" +
            " dept2_name = #{user.dept2Name}," +
            "</if>" +
            "<if test='user.dept2Code != null and user.dept2Code != \"\" '>" +
            " dept2_code = #{user.dept2Code}," +
            "</if>" +
            "<if test='user.dept3Name != null and user.dept3Name != \"\" '>" +
            " dept3_name = #{user.dept3Name}," +
            "</if>" +
            "<if test='user.dept3Code != null and user.dept3Code != \"\" '>" +
            " dept3_code = #{user.dept3Code}," +
            "</if>" +
            "<if test='user.dept4Name != null and user.dept4Name != \"\" '>" +
            " dept4_name = #{user.dept4Name}," +
            "</if>" +
            "<if test='user.dept4Code != null and user.dept4Code != \"\" '>" +
            " dept4_code = #{user.dept4Code}," +
            "</if>" +
            "<if test='user.isInner != null '>" +
            " is_inner = #{user.isInner}," +
            "</if>" +
            "<if test='user.belongPcb != null '>" +
            " belong_pcb = #{user.belongPcb}," +
            "</if>" +
            "<if test='user.belongPcba != null '>" +
            " belong_pcba = #{user.belongPcba}," +
            "</if>" +
            "<if test='user.belongPcbName != null '>" +
            " belong_pcb_name = #{user.belongPcbName}," +
            "</if>" +
            "<if test='user.pcbas != null '>" +
            " pcbas = #{user.pcbas}," +
            "</if>" +
            "<if test='user.pcbs != null '>" +
            " pcbs = #{user.pcbs}," +
            "</if>" +
            "</set>" +
            " where user_id = #{user.userId}" +
            "</script>")
    Integer updateUser(@Param("user") User user);

    @Select("<script>" +
            "select * from " + DB4TableName.USER +
            " <where>" +
            "<if test ='deptId != null '>" +
            " dept_id = #{deptId}" +
            "</if>" +
            "</where>" +
            "</script>")
    @ResultMap("userResultMap")
    List<User> getUserInfoByDept(@Param("deptId") Integer deptId);

    @Select("<script> " +
            "SELECT * " +
            "FROM " + DB4TableName.USER +
            " WHERE user_id IN " +
            "<foreach collection='userIdList' item='userId' open='(' separator=',' close=')'> " +
            "#{userId} " +
            "</foreach>" +
            "</script>")
    @ResultMap("userResultMap")
    List<User> getUserInfoByIdList(@Param("userIdList") List<Integer> userIdList);
}
